package com.tsd.core.excel.export;

import cn.hutool.db.Entity;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.tsd.core.config.Config;
import com.tsd.core.datasource.DataSourceContextHolder;
import com.tsd.core.utils.HlpUtils;
import com.tsd.core.utils.ListUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;

/**
 * @author Hillpool
 */
public class Export {

    public static ExcelExportHelper createHelper(String tempName, int intFirstDataRow) throws IOException {
        ExcelExportHelper helper;
        if (HlpUtils.isEmpty(tempName)) {
            helper = new ExcelExportHelper(intFirstDataRow);
        } else {
            helper = new ExcelExportHelper(new File(tempName), intFirstDataRow);
        }
        helper.setCellStyles(new DefaultCellStyles(helper.getWorkbook()));
        return helper;
    }

    public static ExcelExportHelper setRows(ExcelExportHelper helper, List<LinkedHashMap<String, Object>> list,
                                            Integer no) {
        for (LinkedHashMap<String, Object> map : list) {
            Set<String> keys = map.keySet();
            helper.createRow();
            Integer v = 0;
            for (String key : keys) {
                Object object = map.get(key);
                setExcelCell(helper, v, object, null, false);
                v++;
            }
        }
        return helper;
    }

    public static ExcelExportHelper setRows(ExcelExportHelper helper, List<LinkedHashMap<String, Object>> list, Integer no,
                                            List<Integer> nocolors) {
        CellStyle style = helper.getWorkbook().createCellStyle();
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFillForegroundColor(IndexedColors.YELLOW.index);
        setBorder(style);
        for (LinkedHashMap<String, Object> map : list) {
            Set<String> keys = map.keySet();
            helper.createRow();
            Integer v = 0;
            for (String key : keys) {
                Object object = map.get(key);
                if (nocolors.contains(v)) {
                    setExcelCell(helper, v, object, helper.createStringStyle(), false);
                } else {
                    setExcelCell(helper, v, object, style, false);
                }
                v++;
            }
        }
        return helper;
    }

    public static void setRows(ExcelExportHelper helper, List<LinkedHashMap<String, Object>> list,
                               Set<String> titles) {
        for (LinkedHashMap<String, Object> map : list) {
            helper.createRow();
            int cellIndex = 0;
            for (String key : titles) {
                Object object = map.get(key);
                if (object == null) {
                    object = "";
                }
                setExcelCell(helper, cellIndex, object, helper.getCellStyle(cellIndex), false);
                cellIndex++;
            }
        }
    }

    public static void setRows(ExcelExportHelper helper, List<LinkedHashMap<String, Object>> list,
                               LinkedHashMap<String, String> alias) {
        Set<String> titles = alias.keySet();
        for (LinkedHashMap<String, Object> map : list) {
            helper.createRow();
            int cellIndex = 0;
            for (String key : titles) {
                Object object = map.get(key);
                if (object == null) {
                    object = "";
                }
                setExcelCell(helper, cellIndex, object, helper.getCellStyle(cellIndex), false);
                cellIndex++;
            }
        }
    }

    public static void setRows(ExcelExportHelper helper, List<LinkedHashMap<String, Object>> list, List<Object> titles, int intFirstDataRow) {
        titles = setArrayDepth(titles, intFirstDataRow);
        for (LinkedHashMap<String, Object> map : list) {
            helper.createRow();
            String json = JSON.toJSONString(titles);
            JSONArray array = JSON.parseArray(json);
            eachSetRows(array, helper, map, false);
        }
    }

    public static void setTotalRows(ExcelExportHelper helper, LinkedHashMap<String, Object> map, List<Object> titles, int intFirstDataRow) {
        titles = setArrayDepth(titles, intFirstDataRow);
        helper.createRow();
        String json = JSON.toJSONString(titles);
        JSONArray array = JSON.parseArray(json);
        eachSetRows(array, helper, map, true);
    }

    private static void eachSetRows(JSONArray array, ExcelExportHelper helper, LinkedHashMap<String, Object> map, boolean isFormula) {
        for (int i = 0; i < array.size(); i++) {
            JSONObject object = array.getJSONObject(i);
            if (object.containsKey("children")) {
                JSONArray children = object.getJSONArray("children");
                eachSetRows(children, helper, map, isFormula);
            } else {
                Integer cellIndex = object.getInteger("cellIndex");
                XSSFCellStyle style = getExcelCellStyle(helper, null, object, isFormula);
                if (object.containsKey("contentSize")) {
                    // 加粗标题字体
                    Font normalFont = helper.getWorkbook().createFont();
                    normalFont.setFontName("宋体");
                    if (object.containsKey("contentSize")) {
                        normalFont.setFontHeightInPoints(object.getShort("contentSize"));
                    }
                    style.setFont(normalFont);
                }
                if (object.containsKey("contentShrinkToFit")) {
                    style.setShrinkToFit(true);
                    style.setWrapText(false);
                } else if (object.containsKey("contentWrap")) {
                    style.setWrapText(true);
                }
                if (object.containsKey("borderColor")) {
                    JSONObject border = object.getJSONObject("borderColor");
                    if (border.containsKey("red") && border.containsKey("green") && border.containsKey("blue")) {
                        XSSFColor color = helper.getColor(border.getInteger("red"), border.getInteger("green"), border.getInteger("blue"));
                        style.setTopBorderColor(color);
                        style.setBottomBorderColor(color);
                        style.setLeftBorderColor(color);
                        style.setRightBorderColor(color);
                    }
                }
                String formula = object.getString("formula");
                if (!HlpUtils.isEmpty(formula)) {
                    setExcelCell(helper, cellIndex, map.get(object.getString("value")), style, true);
                    continue;
                }
                style.setLocked(false);
                Object value = map.get(object.getString("value"));
                if (!HlpUtils.isEmpty(value) && value.toString().startsWith("formula:")) {
                    Row row = helper.getRow();
                    String valueStr = value.toString().replaceAll("formula:", "");
                    valueStr = valueStr.replaceAll("\\$currRow\\$", String.valueOf(row.getRowNum() + 1));
                    valueStr = valueStr.replaceAll("\\$row\\$", String.valueOf(row.getRowNum()));
                    setExcelCell(helper, cellIndex, valueStr, style, true);
                    continue;
                }
                setExcelCell(helper, cellIndex, value, style, isFormula);
            }
        }
    }

    private static void setDataFormat(CellStyle style, JSONObject object, DataFormat dataFormat) {
        if (object.containsKey("dataFormat")) {
            String format = object.getString("dataFormat");
            if (!HlpUtils.isEmpty(format)) {
                style.setDataFormat(dataFormat.getFormat(format));
            }
        }
    }

    /**
     * 导出Excel
     *
     * @param helper
     * @return 文件名
     * @throws Exception
     */
    public static String exportExcel(ExcelExportHelper helper) throws Exception {
        String fileName = getFileName(ExcelExportHelper.ExcelType.XLSX.getType());
        // 上传的根目录
        String rootPath = Config.getParameterValue("uploadRootPath");
        helper.export(rootPath + "/" + fileName);
        return fileName;
    }

    /**
     * 导出Excel
     *
     * @param helper
     * @param response
     * @param outputName
     * @throws Exception
     */
    public static void exportExcel(ExcelExportHelper helper, HttpServletResponse response, String outputName) throws Exception {
        response.setHeader("Content-Disposition", "attachment;filenaxme=" + URLEncoder.encode(outputName, "utf-8") + ".xlsx");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        OutputStream output = response.getOutputStream();
        helper.getWorkbook().setSheetName(0, outputName);
        helper.getWorkbook().write(output);
        output.flush();
    }

    public static String templateExcelFilePath(String tempFileName) {
        return Objects.requireNonNull(Export.class.getResource("/xlsx")).getPath() + "/" + tempFileName;
    }

    private static String getFileName(String type) throws Exception {
        // 上传的根目录
        String rootPath = Config.getParameterValue("uploadRootPath");
        String fileName = HlpUtils.date2String(new Date(), HlpUtils.Format_Yyyy_MM_dd_HH_mm_ss_FileName) + Math.random();
        String excelName = rootPath + "/" + fileName + "." + type;
        File file = new File(excelName);
        if (!file.getParentFile().exists()) {
            file.getParentFile().mkdirs();
        }
        if (!file.exists()) {
            file.createNewFile();
        }
        return fileName + "." + type;
    }

    public static void setExcelHeader(ExcelExportHelper helper, Set<String> keys, CellStyle style) {
        setBorder(style);
        helper.createRow();
        int header = 0;
        for (String string : keys) {
            helper.setCellHeader(header, string, style);
            header++;
        }
    }

    /**
     * 设置多表头
     * 标题格式：[{"name":"", "value":"", children:[]}]
     *
     * @param helper
     * @param titles
     */
    public static void setExcelHeader4MultiHead(ExcelExportHelper helper, List<Object> titles, boolean freezeTitle, int intFirstDataRow) {
        int depth = getArrayDepth(titles) + (intFirstDataRow - 1);
        titles = setArrayDepth(titles, (intFirstDataRow - 1));
        int header = 0, floor = 0;
        eachSetHeader(helper, titles, header, floor, depth);
        helper.setRowIndex(depth);
        if (freezeTitle) {
            helper.getSheet().createFreezePane(0, depth, 0, depth);
        }
    }

    private static void eachSetHeader(ExcelExportHelper helper, List<Object> titles, int header, int floor, int depth) {
        Sheet sheet = helper.getSheet();
        for (Object o : titles) {
            String json = JSON.toJSONString(o);
            JSONObject object = JSON.parseObject(json);
            if (object.getInteger("depth") > floor) {
                floor = object.getInteger("depth");
            }
            Row row = sheet.getRow(floor);
            if (row == null) {
                helper.createRow();
            }
            Integer length = object.getInteger("length");
            XSSFCellStyle style = (XSSFCellStyle) helper.createBoldTitleStyle();
            style.setLocked(true);
            if (object.containsKey("titleShrinkToFit")) {
                style.setShrinkToFit(true);
                style.setWrapText(false);
            } else if (object.containsKey("titleWrap")) {
                style.setWrapText(true);
            }
            if (object.containsKey("titleSize") || object.containsKey("titleBold")) {
                // 加粗标题字体
                Font boldTitleFont = helper.getWorkbook().createFont();
                boldTitleFont.setFontName("宋体");
                if (object.containsKey("titleSize")) {
                    boldTitleFont.setFontHeightInPoints(object.getShort("titleSize"));
                }
                if (object.containsKey("titleBold")) {
                    boldTitleFont.setBold(true);
                }
                style.setFont(boldTitleFont);
            }
            if (object.containsKey("titleBg")) {
                JSONObject titleBg = object.getJSONObject("titleBg");
                if (titleBg.containsKey("red") && titleBg.containsKey("green") && titleBg.containsKey("blue")) {
                    XSSFColor color = helper.getColor(titleBg.getInteger("red"), titleBg.getInteger("green"), titleBg.getInteger("blue"));
                    style.setFillForegroundColor(color);
                    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                }
            }
            if (object.containsKey("borderColor")) {
                JSONObject border = object.getJSONObject("borderColor");
                if (border.containsKey("red") && border.containsKey("green") && border.containsKey("blue")) {
                    XSSFColor color = helper.getColor(border.getInteger("red"), border.getInteger("green"), border.getInteger("blue"));
                    style.setTopBorderColor(color);
                    style.setBottomBorderColor(color);
                    style.setLeftBorderColor(color);
                    style.setRightBorderColor(color);
                }
            }
            if (object.containsKey("children")) {
                List<Object> children = object.getJSONArray("children");
                helper.setCellHeader(floor, header, object.getString("name"), style);
                for (int i = 1; i < length; i++) {
                    helper.setCellHeader(floor, (header + i), "", style);
                }
                sheet.addMergedRegion(new CellRangeAddress(floor, floor, header, header + length - 1));
                eachSetHeader(helper, children, header, floor, depth);
            } else {
                helper.setCellHeader(floor, header, object.getString("name"), style, object.getInteger("width"));
                if (floor < depth) {
                    for (int i = floor + 1; i <= depth - 1; i++) {
                        helper.setRowIndex(i);
                        helper.setCellHeader(i, header, "", style);
                    }
                    if (floor < depth - 1) {
                        sheet.addMergedRegion(new CellRangeAddress(floor, depth - 1, header, header));
                    }
                }
            }
            header += length;
        }
    }

    private static List<Object> setArrayDepth(List<Object> titles, int intFirstDataRow) {
        int depth = 0;
        String json = JSON.toJSONString(titles);
        JSONArray array = JSON.parseArray(json);
        eachArrayDepth(array, intFirstDataRow, depth, 0);
        return array.toJavaList(Object.class);
    }

    private static int getArrayDepth(List<Object> titles) {
        int depth = 0;
        String json = JSON.toJSONString(titles);
        JSONArray array = JSON.parseArray(json);
        return eachArrayDepth(array, 1, depth, 0);
    }

    private static int eachArrayDepth(JSONArray array, int floor, int depth, int header) {
        if (array != null && !array.isEmpty()) {
            for (int i = 0; i < array.size(); i++) {
                JSONObject object = array.getJSONObject(i);
                if (floor > depth) {
                    depth = floor;
                }
                object.put("depth", floor);
                int length = 1;
                if (object.containsKey("children")) {
                    JSONArray children = object.getJSONArray("children");
                    if (!children.isEmpty()) {
                        int d = eachArrayDepth(children, floor + 1, depth, header);
                        if (d > depth) {
                            depth = d;
                        }
                        int childLength = 0;
                        for (int j = 0; j < children.size(); j++) {
                            JSONObject child = children.getJSONObject(j);
                            childLength += child.getInteger("length");
                        }
                        length = childLength;
                        object.put("length", childLength);
                    } else {
                        object.put("length", 1);
                    }
                } else {
                    object.put("cellIndex", header);
                    object.put("length", 1);
                    int width = object.getString("name").getBytes(StandardCharsets.UTF_8).length;
                    if (object.containsKey("width")) {
                        width = object.getInteger("width");
                    }
                    Integer minWidth = object.getInteger("minWidth");
                    if (minWidth != null && width < minWidth) {
                        width = minWidth;
                    }
                    object.put("width", width * 256 + 150);
                }
                header += length;
            }
        }
        return depth;
    }

    public static void setExcelHeader(ExcelExportHelper helper, Set<String> keys, CellStyle style, List<Integer> nocolors) {
        setBorder(style);
        helper.createRow();
        int header = 0;
        CellStyles styles = new DefaultCellStyles(helper.getWorkbook());
        for (String string : keys) {
            helper.setCellHeader(header, string, style);
            if (nocolors.contains(header)) {
                helper.setCellHeader(header, string, styles.getStringStyle());
            } else {
                helper.setCellHeader(header, string, style);
            }
            header++;
        }
    }

    /**
     * 设置Excel标题
     *
     * @param helper helper
     * @param alias  Excel标题Map key-标题字段 value-标题名称
     * @param style  style
     */
    public static void setExcelHeader(ExcelExportHelper helper, LinkedHashMap<String, String> alias, CellStyle style) {
        setBorder(style);
        helper.createRow();
        int header = 0;
        Collection<String> keys = alias.values();
        for (String string : keys) {
            helper.setCellHeader(header, string, style);
            header++;
        }
        Sheet sheet = helper.getSheet();
        sheet.createFreezePane(0, 1, 0, 1);
    }

    private static void setExcelCell(ExcelExportHelper helper, Integer index, Object object, CellStyle style, boolean isFormula) {
        if (object == null) {
            if (style == null) {
                style = helper.createStringStyle();
            }
            Cell cell = CellUtil.getCell(helper.getRow(), index);
            cell.setCellStyle(style);
            return;
        }
        if (isFormula && !"合计".equals(object)) {
            helper.setCellFormula(index, object.toString(), style);
            return;
        }
        if (object instanceof Integer) {
            if (style == null) {
                style = helper.createIntegerStyle();
            }
            helper.setCell(index, Integer.parseInt(object.toString()), style);
        } else if (object instanceof String) {
            if (style == null) {
                style = helper.createStringStyle();
            }
            helper.setCell(index, object.toString(), style);
        } else if (object instanceof Double) {
            if (style == null) {
                style = helper.createNumberStyle();
            }
            helper.setCell(index, Double.parseDouble(object.toString()), style);
        } else if (object instanceof BigDecimal) {
            if (style == null) {
                style = helper.createNumberStyle();
            }
            helper.setCell(index, ((BigDecimal) object).doubleValue(), style);
        } else if (object instanceof Float) {
            if (style == null) {
                style = helper.createNumberStyle();
            }
            helper.setCell(index, Float.parseFloat(object.toString()), style);
        } else if (object instanceof Date) {
            if (style == null) {
                style = helper.createDateStyle();
            }
            helper.setCell(index, (Date) object, style);
        } else if (object instanceof Long) {
            if (style == null) {
                style = helper.createNumberStyle();
            }
            helper.setCell(index, Long.parseLong(object.toString()), style);
        } else {
            if (style == null) {
                style = helper.createStringStyle();
            }
            helper.setCell(index, object.toString(), style);
        }
    }

    private static void setBorder(CellStyle style) {
        if (style != null) {
            // 设置边框
            style.setBorderRight(BorderStyle.THIN);
            style.setRightBorderColor(IndexedColors.BLACK.getIndex());

            style.setBorderLeft(BorderStyle.THIN);
            style.setLeftBorderColor(IndexedColors.BLACK.getIndex());

            style.setBorderTop(BorderStyle.THIN);
            style.setTopBorderColor(IndexedColors.BLACK.getIndex());

            style.setBorderBottom(BorderStyle.THIN);
            style.setBottomBorderColor(IndexedColors.BLACK.getIndex());

            style.setAlignment(HorizontalAlignment.CENTER);//水平居中
            style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        }
    }

    /**
     * 设置合计行
     *
     * @param helper
     * @param titleList
     */
    public static void setTotalRows(ExcelExportHelper helper, List<Object> titleList, int intFirstDataRow) {
        int depth = getArrayDepth(titleList);
        JSONArray array = getExcelHeader4MultiHeadList(titleList);
        LinkedHashMap<String, Object> totalMap = new LinkedHashMap<>();
        int rowNum = helper.getSheet().getLastRowNum();
        int cellIndex = 0;
        for (int i = 0; i < array.size(); i++) {
            JSONObject object = array.getJSONObject(i);
            if (object.containsKey("sum")) {
                if (cellIndex == 0) {
                    cellIndex = i;
                }
                String radix = helper.toRadix(i);
                totalMap.put(object.getString("value"), String.format("SUM(%s%d:%s%d)", radix, (intFirstDataRow + depth), radix, (rowNum + 1)));
            }
        }
        if (totalMap.isEmpty()) {
            return;
        }
        totalMap.put(array.getJSONObject(0).getString("value"), "合计");
        setTotalRows(helper, totalMap, titleList, intFirstDataRow);
        if (cellIndex > 1) {
            helper.getSheet().addMergedRegion(new CellRangeAddress(rowNum + 1, rowNum + 1, 0, cellIndex - 1));
        }
    }

    public static JSONArray getExcelHeader4MultiHeadList(List<Object> titles) {
        JSONArray array = JSON.parseArray(JSON.toJSONString(titles));
        return eachExcelHeader4MultiHeadList(array);
    }

    private static JSONArray eachExcelHeader4MultiHeadList(JSONArray array) {
        JSONArray jsonArray = new JSONArray();
        if (array != null && !array.isEmpty()) {
            for (int i = 0; i < array.size(); i++) {
                JSONObject object = array.getJSONObject(i);
                if (!object.containsKey("children")) {
                    jsonArray.add(object);
                    continue;
                }
                JSONArray childList = eachExcelHeader4MultiHeadList(object.getJSONArray("children"));
                object.remove("children");
                if (!childList.isEmpty()) {
                    jsonArray.addAll(childList);
                }
            }
        }
        return jsonArray;
    }

    public static void setExtMergedRow(ExcelExportHelper helper, List<Object> titleList, JSONArray array, int intFirstDataRow) {
        if (array == null || array.isEmpty()) {
            return;
        }
        JSONArray titleArray = getExcelHeader4MultiHeadList(setArrayDepth(titleList, intFirstDataRow));
        int rowNum = helper.getSheet().getLastRowNum();
        helper.setRowIndex(rowNum + 1);
        for (int i = 0; i < array.size(); i++) {
            JSONObject object = array.getJSONObject(i);
            helper.createRow();
            int firstCol = 0, lastCol = 0;
            CellStyle style = helper.createStringStyle();
            for (int j = 0; j < titleArray.size(); j++) {
                JSONObject titleObject = titleArray.getJSONObject(j);
                Integer cellIndex = titleObject.getInteger("cellIndex");
                if (firstCol > cellIndex) {
                    firstCol = cellIndex;
                }
                if (lastCol < cellIndex) {
                    lastCol = cellIndex;
                }
            }
            for (int j = 0; j < titleArray.size(); j++) {
                JSONObject titleObject = titleArray.getJSONObject(j);
                Integer cellIndex = titleObject.getInteger("cellIndex");
                if (firstCol != cellIndex) {
                    setExcelCell(helper, cellIndex, null, style, false);
                }
            }
            if (object.containsKey("red") && object.containsKey("green") && object.containsKey("blue")) {
                XSSFFont font = (XSSFFont) helper.getWorkbook().createFont();
                font.setFontHeightInPoints((short) 10);
                font.setFontName("宋体");
                XSSFColor color = helper.getColor(object.getInteger("red"), object.getInteger("green"), object.getInteger("blue"));
                font.setColor(color);
                style.setFont(font);
            }
            style.setAlignment(HorizontalAlignment.LEFT);
            setExcelCell(helper, firstCol, object.getString("text"), style, false);
            int rowIndex = helper.getRowIndex() - 1;
            CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex, firstCol, lastCol);
            helper.getSheet().addMergedRegion(region);
        }
    }

    public static void setRowMergedRegion(ExcelExportHelper helper, List<Object> titleList, JSONObject object) {
        if (object.containsKey("title")) {
            JSONArray array = object.getJSONArray("title");
            if (array != null && !array.isEmpty()) {
                for (int i = 0; i < array.size(); i++) {
                    JSONArray regionArray = array.getJSONArray(i);
                    setRowMergedRegion(helper, regionArray, 0);
                }
            }
        }
        if (object.containsKey("data")) {
            JSONArray array = object.getJSONArray("data");
            if (array != null && !array.isEmpty()) {
                int depth = getArrayDepth(titleList);
                for (int i = 0; i < array.size(); i++) {
                    JSONArray regionArray = array.getJSONArray(i);
                    setRowMergedRegion(helper, regionArray, depth);
                }
            }
        }
    }

    private static void setRowMergedRegion(ExcelExportHelper helper, JSONArray array, int plus) {
        if (array.size() != 4) {
            return;
        }
        Sheet sheet = helper.getSheet();
        int startRow = array.getIntValue(0) + plus;
        int endRow = array.getIntValue(1) + plus;
        int startColumn = array.getIntValue(2);
        int endColumn = array.getIntValue(3);
        //获取所有的单元格
        int sheetMergeCount = helper.getSheet().getNumMergedRegions();
        //用于保存要移除的那个合并单元格序号
        List<Integer> indexList = new ArrayList<>();
        for (int i = 0; i < sheetMergeCount; i++) {
            //获取第i个单元格
            CellRangeAddress ca = helper.getSheet().getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (startRow <= firstRow && endRow >= lastRow && startColumn <= firstColumn && endColumn >= lastColumn) {
                indexList.add(i);
            }
        }
        sheet.removeMergedRegions(indexList);
        for (int i = startRow; i <= endRow; i++) {
            Row row = CellUtil.getRow(i, sheet);
            for (int j = startColumn; j <= endColumn; j++) {
                Cell cell = row.getCell(j);
                if (cell == null) {
                    row.createCell(j).setCellStyle(helper.createStringStyle());
                }
            }
        }
        CellRangeAddress region = new CellRangeAddress(startRow, endRow, startColumn, endColumn);
        sheet.addMergedRegion(region);
    }

    public static void setTableBorder(ExcelExportHelper helper, JSONObject object) {
        if (!object.containsKey("style") || !object.containsKey("area")) {
            return;
        }
        BorderStyle borderStyle = getBorderStyle(object.getString("style"));
        if (borderStyle == null) {
            return;
        }
        JSONArray array = object.getJSONArray("area");
        if (array.size() != 4) {
            return;
        }
        int startRow = array.getIntValue(0);
        if (object.containsKey("plus")) {
            startRow += object.getInteger("plus");
        }
        int endRow = array.getIntValue(1);
        if (object.containsKey("plus")) {
            endRow += object.getInteger("plus");
        }
        int startColumn = array.getIntValue(2);
        int endColumn = array.getIntValue(3);
        CellRangeAddress range = new CellRangeAddress(startRow, endRow, startColumn, endColumn);
        setBorderStyle(helper, range, object);
        setBorderColor(helper, range, object);
    }

    private static void setBorderStyle(ExcelExportHelper helper, CellRangeAddress region, JSONObject border) {
        if (!border.containsKey("style")) {
            return;
        }
        if ((border.get("style") instanceof String)) {
            BorderStyle borderStyle = getBorderStyle(border.getString("style"));
            if (borderStyle == null) {
                return;
            }
            setBorderTop(helper, region, null, borderStyle);
            setBorderBottom(helper, region, null, borderStyle);
            setBorderLeft(helper, region, null, borderStyle);
            setBorderRight(helper, region, null, borderStyle);
        } else if ((border.get("style") instanceof List)) {
            // 顺序上、下、左、右
            JSONArray styleArray = border.getJSONArray("style");
            if (!styleArray.isEmpty()) {
                if (styleArray.size() == 1) {
                    BorderStyle borderStyle = getBorderStyle(styleArray.getString(0));
                    if (borderStyle == null) {
                        return;
                    }
                    setBorderTop(helper, region, null, borderStyle);
                    setBorderBottom(helper, region, null, borderStyle);
                    setBorderLeft(helper, region, null, borderStyle);
                    setBorderRight(helper, region, null, borderStyle);
                } else {
                    for (int j = 0; j < 4; j++) {
                        if (j >= styleArray.size()) {
                            continue;
                        }
                        BorderStyle borderStyle = getBorderStyle(styleArray.getString(j));
                        if (borderStyle == null) {
                            continue;
                        }
                        if (j == 0) {
                            setBorderTop(helper, region, null, borderStyle);
                        } else if (j == 1) {
                            setBorderBottom(helper, region, null, borderStyle);
                        } else if (j == 2) {
                            setBorderLeft(helper, region, null, borderStyle);
                        } else {
                            setBorderRight(helper, region, null, borderStyle);
                        }
                    }
                }
            }
        }
    }

    private static void setBorderColor(ExcelExportHelper helper, CellRangeAddress region, JSONObject border) {
        if (!border.containsKey("color")) {
            return;
        }
        if ((border.get("color") instanceof Map)) {
            JSONObject colorObj = border.getJSONObject("color");
            if (colorObj.containsKey("red") && colorObj.containsKey("green") && colorObj.containsKey("blue")) {
                XSSFColor color = helper.getColor(colorObj.getInteger("red"), colorObj.getInteger("green"), colorObj.getInteger("blue"));
                setBorderTop(helper, region, color, null);
                setBorderBottom(helper, region, color, null);
                setBorderLeft(helper, region, color, null);
                setBorderRight(helper, region, color, null);
            }
        } else if ((border.get("color") instanceof List)) {
            // 顺序上、下、左、右
            JSONArray styleArray = border.getJSONArray("color");
            if (!styleArray.isEmpty()) {
                if (styleArray.size() == 1) {
                    JSONObject colorObj = styleArray.getJSONObject(0);
                    if (colorObj.containsKey("red") && colorObj.containsKey("green") && colorObj.containsKey("blue")) {
                        XSSFColor color = helper.getColor(colorObj.getInteger("red"), colorObj.getInteger("green"), colorObj.getInteger("blue"));
                        setBorderTop(helper, region, color, null);
                        setBorderBottom(helper, region, color, null);
                        setBorderLeft(helper, region, color, null);
                        setBorderRight(helper, region, color, null);
                    }
                } else {
                    for (int j = 0; j < 4; j++) {
                        if (j >= styleArray.size()) {
                            continue;
                        }
                        JSONObject colorObj = styleArray.getJSONObject(j);
                        if (colorObj.containsKey("red") && colorObj.containsKey("green") && colorObj.containsKey("blue")) {
                            XSSFColor color = helper.getColor(colorObj.getInteger("red"), colorObj.getInteger("green"), colorObj.getInteger("blue"));
                            if (j == 0) {
                                setBorderTop(helper, region, color, null);
                            } else if (j == 1) {
                                setBorderBottom(helper, region, color, null);
                            } else if (j == 2) {
                                setBorderLeft(helper, region, color, null);
                            } else {
                                setBorderRight(helper, region, color, null);
                            }
                        }
                    }
                }
            }
        }
    }

    private static void setBorderTop(ExcelExportHelper helper, CellRangeAddress region, XSSFColor color, BorderStyle borderStyle) {
        int colStart = region.getFirstColumn();
        int colEnd = region.getLastColumn();
        int rowIndex = region.getFirstRow();
        Row row = CellUtil.getRow(rowIndex, helper.getSheet());
        for (int i = colStart; i <= colEnd; i++) {
            Cell cell = CellUtil.getCell(row, i);
            XSSFCellStyle style = (XSSFCellStyle) cell.getCellStyle();
            if (color != null) {
                style.setTopBorderColor(color);
            }
            if (borderStyle != null) {
                style.setBorderTop(borderStyle);
            }
            cell.setCellStyle(style);
        }
    }

    private static void setBorderBottom(ExcelExportHelper helper, CellRangeAddress region, XSSFColor color, BorderStyle borderStyle) {
        int colStart = region.getFirstColumn();
        int colEnd = region.getLastColumn();
        int rowIndex = region.getLastRow();
        Row row = CellUtil.getRow(rowIndex, helper.getSheet());
        for (int i = colStart; i <= colEnd; i++) {
            Cell cell = CellUtil.getCell(row, i);
            XSSFCellStyle style = (XSSFCellStyle) cell.getCellStyle();
            if (color != null) {
                style.setBottomBorderColor(color);
            }
            if (borderStyle != null) {
                style.setBorderBottom(borderStyle);
            }
            cell.setCellStyle(style);
        }
    }

    private static void setBorderLeft(ExcelExportHelper helper, CellRangeAddress region, XSSFColor color, BorderStyle borderStyle) {
        int rowStart = region.getFirstRow();
        int rowEnd = region.getLastRow();
        int column = region.getFirstColumn();
        for (int i = rowStart; i <= rowEnd; i++) {
            Row row = CellUtil.getRow(i, helper.getSheet());
            Cell cell = CellUtil.getCell(row, column);
            XSSFCellStyle style = (XSSFCellStyle) cell.getCellStyle();
            if (color != null) {
                style.setLeftBorderColor(color);
            }
            if (borderStyle != null) {
                style.setBorderLeft(borderStyle);
            }
            cell.setCellStyle(style);
        }
    }

    private static void setBorderRight(ExcelExportHelper helper, CellRangeAddress region, XSSFColor color, BorderStyle borderStyle) {
        int rowStart = region.getFirstRow();
        int rowEnd = region.getLastRow();
        int column = region.getLastColumn();
        for (int i = rowStart; i <= rowEnd; i++) {
            Row row = CellUtil.getRow(i, helper.getSheet());
            Cell cell = CellUtil.getCell(row, column);
            XSSFCellStyle style = (XSSFCellStyle) cell.getCellStyle();
            if (color != null) {
                style.setRightBorderColor(color);
            }
            if (borderStyle != null) {
                style.setBorderRight(borderStyle);
            }
            cell.setCellStyle(style);
        }
    }

    private static BorderStyle getBorderStyle(String style) {
        if (style == null) {
            return null;
        }
        if ("none".equalsIgnoreCase(style)) {
            return BorderStyle.NONE;
        }
        if ("thin".equalsIgnoreCase(style)) {
            return BorderStyle.THIN;
        }
        if ("medium".equalsIgnoreCase(style)) {
            return BorderStyle.MEDIUM;
        }
        if ("dashed".equalsIgnoreCase(style)) {
            return BorderStyle.DASHED;
        }
        if ("dotted".equalsIgnoreCase(style)) {
            return BorderStyle.DOTTED;
        }
        if ("thick".equalsIgnoreCase(style)) {
            return BorderStyle.THICK;
        }
        if ("double".equalsIgnoreCase(style)) {
            return BorderStyle.DOUBLE;
        }
        if ("hair".equalsIgnoreCase(style)) {
            return BorderStyle.HAIR;
        }
        if ("medium_dashed".equalsIgnoreCase(style)) {
            return BorderStyle.MEDIUM_DASHED;
        }
        if ("dash_dot".equalsIgnoreCase(style)) {
            return BorderStyle.DASH_DOT;
        }
        if ("medium_dash_dot".equalsIgnoreCase(style)) {
            return BorderStyle.MEDIUM_DASH_DOT;
        }
        if ("dash_dot_dot".equalsIgnoreCase(style)) {
            return BorderStyle.DASH_DOT_DOT;
        }
        if ("medium_dash_dot_dot".equalsIgnoreCase(style)) {
            return BorderStyle.MEDIUM_DASH_DOT_DOT;
        }
        if ("slanted_dash_dot".equalsIgnoreCase(style)) {
            return BorderStyle.SLANTED_DASH_DOT;
        }
        return null;
    }

    public static void updateRow(ExcelExportHelper helper, JSONArray array) {
        if (array == null || array.isEmpty()) {
            return;
        }
        Sheet sheet = helper.getSheet();
        for (int i = 0; i < array.size(); i++) {
            JSONObject object = array.getJSONObject(i);
            if (!object.containsKey("position")) {
                continue;
            }
            JSONArray position = object.getJSONArray("position");
            if (position.isEmpty()) {
                continue;
            }
            Integer rowIndex = position.getInteger(0);
            Integer cellIndex = position.getInteger(1);
            String value = object.getString("value");
            Row row = CellUtil.getRow(rowIndex, sheet);
            Cell cell = row.getCell(cellIndex);
            if (!HlpUtils.isEmpty(value)) {
                helper.setRowIndex(rowIndex);
                helper.setRow(row);
                setExcelCell(helper, cellIndex, object.get("value"), helper.createStringStyle(cell), object.containsKey("formula"));
            }
            cell = row.getCell(cellIndex);
            if (cell == null) {
                cell = row.createCell(cellIndex);
            }
            XSSFCellStyle style = getExcelCellStyle(helper, cell, object, false);
            cell.setCellStyle(style);
            if (object.containsKey("border")) {
                JSONObject border = object.getJSONObject("border");
                CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex, cellIndex, cellIndex);
                setBorderStyle(helper, region, border);
                setBorderColor(helper, region, border);
            }
        }
    }

    private static XSSFCellStyle getExcelCellStyle(ExcelExportHelper helper, Cell cell, JSONObject object, boolean isFormula) {
        DataFormat df = helper.getWorkbook().createDataFormat();
        CellStyle cellStyle = helper.createStringStyle(cell);
        if (object.containsKey("amount")) {
            cellStyle = helper.createCurrencyStyle(cell);
            if (!object.containsKey("dataFormat")) {
                object.put("dataFormat", DefaultCellStyles.ExcelFormat.CURRENCY.getPattern());
            }
            cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        } else if (object.containsKey("number")) {
            cellStyle = helper.createNumberStyle(null);
            if (!object.containsKey("dataFormat")) {
                object.put("dataFormat", DefaultCellStyles.ExcelFormat.NUMBER.getPattern());
            }
        } else if (object.containsKey("integer")) {
            cellStyle = helper.createIntegerStyle(null);
            if (!object.containsKey("dataFormat")) {
                object.put("dataFormat", DefaultCellStyles.ExcelFormat.INTEGER.getPattern());
            }
        } else if (object.containsKey("date")) {
            cellStyle = helper.createDateStyle(null);
            if (!object.containsKey("dataFormat")) {
                object.put("dataFormat", DefaultCellStyles.ExcelFormat.DATE.getPattern());
            }
        } else if (object.containsKey("datetime")) {
            cellStyle = helper.createDateStyle(null);
            if (!object.containsKey("dataFormat")) {
                object.put("dataFormat", DefaultCellStyles.ExcelFormat.DATETIME.getPattern());
            }
        } else {
            if (!object.containsKey("dataFormat")) {
                object.put("dataFormat", DefaultCellStyles.ExcelFormat.STRING.getPattern());
            }
        }
        if (isFormula) {
            cellStyle = helper.createFormulaStyle(null);
        }
        XSSFCellStyle style = (XSSFCellStyle) cellStyle;
        XSSFFont font = style.getFont();
        if (object.containsKey("fontName")) {
            font.setFontName(object.getString("fontName"));
        }
        if (object.containsKey("fontSize")) {
            font.setFontHeightInPoints(object.getShort("fontSize"));
        }
        if (object.containsKey("fontBold")) {
            font.setBold(true);
        }
        if (object.containsKey("color")) {
            JSONObject border = object.getJSONObject("color");
            if (border.containsKey("red") && border.containsKey("green") && border.containsKey("blue")) {
                XSSFColor color = helper.getColor(border.getInteger("red"), border.getInteger("green"), border.getInteger("blue"));
                font.setColor(color);
            }
        }
        if (object.containsKey("alignment")) {
            String alignment = object.getString("alignment");
            if ("center".equals(alignment)) {
                style.setAlignment(HorizontalAlignment.CENTER);
            } else if ("left".equals(alignment)) {
                style.setAlignment(HorizontalAlignment.LEFT);
            } else if ("right".equals(alignment)) {
                style.setAlignment(HorizontalAlignment.RIGHT);
            }
        }
        if (object.containsKey("vertical")) {
            String vertical = object.getString("vertical");
            if ("center".equals(vertical)) {
                style.setVerticalAlignment(VerticalAlignment.CENTER);
            } else if ("top".equals(vertical)) {
                style.setVerticalAlignment(VerticalAlignment.TOP);
            } else if ("bottom".equals(vertical)) {
                style.setVerticalAlignment(VerticalAlignment.BOTTOM);
            } else if ("justify".equals(vertical)) {
                style.setVerticalAlignment(VerticalAlignment.JUSTIFY);
            } else if ("distributed".equals(vertical)) {
                style.setVerticalAlignment(VerticalAlignment.DISTRIBUTED);
            }
        }
        style.setFont(font);
        setDataFormat(style, object, df);
        if (object.containsKey("shrinkToFit")) {
            style.setShrinkToFit(true);
            style.setWrapText(false);
        } else if (object.containsKey("wrap")) {
            style.setWrapText(true);
        }
        return style;
    }

    public static void resetTableBorder(ExcelExportHelper helper, JSONArray array) {
        if (array == null || array.isEmpty()) {
            return;
        }
        for (int i = 0; i < array.size(); i++) {
            JSONObject object = array.getJSONObject(i);
            if (!object.containsKey("area")) {
                continue;
            }
            JSONArray area = object.getJSONArray("area");
            if (area.size() != 4) {
                return;
            }
            int startRow = area.getIntValue(0);
            int endRow = area.getIntValue(1);
            int startColumn = area.getIntValue(2);
            int endColumn = area.getIntValue(3);
            for (int row = startRow; row <= endRow; row++) {
                for (int column = startColumn; column <= endColumn; column++) {
                    CellRangeAddress range = new CellRangeAddress(row, row, column, column);
                    setBorderStyle(helper, range, object);
                    setBorderColor(helper, range, object);
                }
            }
        }
    }

    public static void setMergedRowRegion(ExcelExportHelper helper, List<Object> titleList, List<LinkedHashMap<String, Object>> dataList, JSONObject object, int intFirstDataRow) {
        if (object.isEmpty()) {
            return;
        }
        String indexField = object.getString("index");
        if (HlpUtils.isEmpty(indexField)) {
            return;
        }
        JSONArray keyArray = object.containsKey("key") ? object.getJSONArray("key") : new JSONArray();
        JSONArray mergedFieldArray = object.getJSONArray("merged");
        if (mergedFieldArray == null || mergedFieldArray.isEmpty()) {
            return;
        }
        titleList = setArrayDepth(titleList, intFirstDataRow);
        JSONArray array = getExcelHeader4MultiHeadList(titleList);
        Sheet sheet = helper.getSheet();
        for (int i = 0; i < mergedFieldArray.size(); i++) {
            String field = mergedFieldArray.getString(i);
            if (keyArray.isEmpty()) {
                keyArray.add(field);
            }
            JSONObject column = getExcelHeader4MultiHead(array, field);
            if (column == null) {
                continue;
            }
            int cellIndex = column.getIntValue("cellIndex");
            List<Object> unionList = ListUtil.map(dataList, t -> getKeyValue(t, keyArray));
            for (Object obj : unionList) {
                if (HlpUtils.isEmpty(obj)) {
                    continue;
                }
                List<LinkedHashMap<String, Object>> filterList = ListUtil.filter(dataList, t -> getKeyValue(t, keyArray).equals(obj));
                if (HlpUtils.isEmptyList(filterList) || filterList.size() == 1) {
                    continue;
                }
                int first = ListUtil.indexOf(dataList, t -> t.getOrDefault(indexField, "").equals(filterList.get(0).getOrDefault(indexField, "")));
                int last = ListUtil.indexOf(dataList, t -> t.getOrDefault(indexField, "").equals(filterList.get(filterList.size()-1).getOrDefault(indexField, "")));
                CellRangeAddress range = new CellRangeAddress(first+intFirstDataRow, last+intFirstDataRow, cellIndex, cellIndex);
                sheet.addMergedRegion(range);
            }
        }
    }

    private static JSONObject getExcelHeader4MultiHead(JSONArray array, String field) {
        for (int i = 0; i < array.size(); i++) {
            JSONObject object = array.getJSONObject(i);
            if (field.equals(object.getString("value"))) {
                return object;
            }
        }
        return null;
    }

    private static String getKeyValue(LinkedHashMap<String, Object> map, JSONArray array) {
        List<Object> list = new ArrayList<>();
        if (array != null && !array.isEmpty()) {
            for (int i = 0; i < array.size(); i++) {
                Object object = map.getOrDefault(array.getString(i), "");
                list.add(object);
            }
        }
        return StringUtils.join(list, "_");
    }

    public static void setLockColumn(ExcelExportHelper helper, Object object, int intFirstDataRow) {
        JSONObject lockObject = JSON.parseObject(JSON.toJSONString(object));
        if (lockObject.isEmpty() || !lockObject.containsKey("area")) {
            return;
        }
        JSONArray lockArea = lockObject.getJSONArray("area");
        if (lockArea.isEmpty()) {
            return;
        }
        XSSFColor areaBgColor = null, areaFontColor = null;
        if (lockObject.containsKey("bg")) {
            JSONObject lockAreaBg = lockObject.getJSONObject("bg");
            if (lockAreaBg.containsKey("red") && lockAreaBg.containsKey("green") && lockAreaBg.containsKey("blue")) {
                areaBgColor = helper.getColor(lockAreaBg.getInteger("red"), lockAreaBg.getInteger("green"), lockAreaBg.getInteger("blue"));
            }
        }
        if (lockObject.containsKey("color")) {
            JSONObject lockAreaFontColor = lockObject.getJSONObject("color");
            if (lockAreaFontColor.containsKey("red") && lockAreaFontColor.containsKey("green") && lockAreaFontColor.containsKey("blue")) {
                areaFontColor = helper.getColor(lockAreaFontColor.getInteger("red"), lockAreaFontColor.getInteger("green"), lockAreaFontColor.getInteger("blue"));
            }
        }
        Sheet sheet = helper.getSheet();
        for (int i = intFirstDataRow; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            for (int j = 0; j < lockArea.size(); j++) {
                int index = lockArea.getIntValue(j);
                if (index <= row.getPhysicalNumberOfCells()) {
                    Cell cell = row.getCell(index);
                    if (cell != null) {
                        XSSFCellStyle style = (XSSFCellStyle) cell.getCellStyle();
                        style.setLocked(true);
                        if (areaBgColor != null) {
                            style.setFillForegroundColor(areaBgColor);
                            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                        }
                        if (areaFontColor != null) {
                            style.getFont().setColor(areaFontColor);
                        }
                        cell.setCellStyle(style);
                    }
                }
            }
        }
        Entity entity = DataSourceContextHolder.getAccountSet();
        sheet.protectSheet(entity.getStr("sid"));
    }
}
